﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using CommonLibrary.Data.Sql;

using System.Data.OracleClient;

namespace CommonLibrary.Data.NetOracle
{
    public static class ClNetOracle
    {
        #region ConnectionString

        #region ExecuteNonQuery

        public static int ExecuteNonQuery(string in_ConnectionString, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            return NetOracleHelper.ExecuteNonQuery(in_ConnectionString, cmdType, in_sql, in_paras);
        }

        public static int ExecuteNonQuery(string in_ConnectionString, CommandType cmdType, string in_sql)
        {
            return ExecuteNonQuery(in_ConnectionString, cmdType, in_sql, null);
        }

        public static int ExecuteNonQuery(string in_ConnectionString, string in_sql, OracleParameter[] in_paras)
        {
            return ExecuteNonQuery(in_ConnectionString, CommandType.Text, in_sql, in_paras);
        }

        public static int ExecuteNonQuery(string in_ConnectionString, string in_sql)
        {
            return ExecuteNonQuery(in_ConnectionString, in_sql, null);
        }


        #endregion


        #region ExecuteScalar

        public static object ExecuteScalar(string in_ConnectionString, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            return NetOracleHelper.ExecuteScalar(in_ConnectionString, cmdType, in_sql, in_paras);
        }

        public static object ExecuteScalar(string in_ConnectionString, CommandType cmdType, string in_sql)
        {
            return ExecuteScalar(in_ConnectionString, cmdType, in_sql, null);
        }

        public static object ExecuteScalar(string in_ConnectionString, string in_sql, OracleParameter[] in_paras)
        {
            return ExecuteScalar(in_ConnectionString, CommandType.Text, in_sql, in_paras);
        }

        public static object ExecuteScalar(string in_ConnectionString, string in_sql)
        {
            return ExecuteScalar(in_ConnectionString, in_sql, null);
        }

        #endregion


        #region ExecuteDataSet

        public static DataSet ExecuteDataSet(string in_ConnectionString, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            return NetOracleHelper.ExecuteDataSet(in_ConnectionString, cmdType, in_sql, in_paras);
        }

        public static DataSet ExecuteDataSet(string in_ConnectionString, CommandType cmdType, string in_sql)
        {
            return ExecuteDataSet(in_ConnectionString, cmdType, in_sql, null);
        }

        public static DataSet ExecuteDataSet(string in_ConnectionString, string in_sql, OracleParameter[] in_paras)
        {
            return ExecuteDataSet(in_ConnectionString, CommandType.Text, in_sql, in_paras);
        }

        public static DataSet ExecuteDataSet(string in_ConnectionString, string in_sql)
        {
            return ExecuteDataSet(in_ConnectionString, in_sql, null);
        }


        #endregion


        #region ExecuteDataTable

        public static DataTable ExecuteDataTable(string in_ConnectionString, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            DataSet ds = NetOracleHelper.ExecuteDataSet(in_ConnectionString, cmdType, in_sql, in_paras);
            if (ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }

        public static DataTable ExecuteDataTable(string in_ConnectionString, CommandType cmdType, string in_sql)
        {
            return ExecuteDataTable(in_ConnectionString, cmdType, in_sql, null);
        }

        public static DataTable ExecuteDataTable(string in_ConnectionString, string in_sql, OracleParameter[] in_paras)
        {
            return ExecuteDataTable(in_ConnectionString, CommandType.Text, in_sql, in_paras);
        }

        public static DataTable ExecuteDataTable(string in_ConnectionString, string in_sql)
        {
            return ExecuteDataTable(in_ConnectionString, in_sql, null);
        }

        #endregion


        #region ExecuteReader 用完后一定要使用reader.Close

        public static OracleDataReader ExecuteReader(string in_ConnectionString, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            return NetOracleHelper.ExecuteReader(in_ConnectionString, cmdType, in_sql, in_paras);
        }

        public static OracleDataReader ExecuteReader(string in_ConnectionString, CommandType cmdType, string in_sql)
        {
            return ExecuteReader(in_ConnectionString, cmdType, in_sql, null);
        }

        public static OracleDataReader ExecuteReader(string in_ConnectionString, string in_sql, OracleParameter[] in_paras)
        {
            return ExecuteReader(in_ConnectionString, CommandType.Text, in_sql, in_paras);
        }

        public static OracleDataReader ExecuteReader(string in_ConnectionString, string in_sql)
        {
            return ExecuteReader(in_ConnectionString, in_sql, null);
        }

        #endregion


        #region ExecuteNonQueryTransaction

        public static int ExecuteNonQueryTransaction(string in_ConnectionString, CommandType cmdType, string in_sql, OracleParameter[] in_paras, bool in_commit)
        {
            using (OracleConnection cn = new OracleConnection(in_ConnectionString))
            {
                cn.Open();
                OracleTransaction tran = cn.BeginTransaction();

                int i = tran.ClExecuteNonQuery(cmdType, in_sql, in_paras);


                if ((i >= 0) && in_commit)
                {
                    tran.Commit();
                    return i;
                }

                tran.Rollback();
                return i;


            }
        }

        public static int ExecuteNonQueryTransaction(string in_ConnectionString, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            return ExecuteNonQueryTransaction(in_ConnectionString, cmdType, in_sql, in_paras, true);
        }

        public static int ExecuteNonQueryTransaction(string in_ConnectionString, CommandType cmdType, string in_sql)
        {
            return ExecuteNonQueryTransaction(in_ConnectionString, cmdType, in_sql, null);
        }

        public static int ExecuteNonQueryTransaction(string in_ConnectionString, string in_sql, OracleParameter[] in_paras, bool in_commit)
        {
            return ExecuteNonQueryTransaction(in_ConnectionString, CommandType.Text, in_sql, in_paras, in_commit);
        }

        public static int ExecuteNonQueryTransaction(string in_ConnectionString, string in_sql, OracleParameter[] in_paras)
        {
            return ExecuteNonQueryTransaction(in_ConnectionString, in_sql, in_paras, true);
        }

        public static int ExecuteNonQueryTransaction(string in_ConnectionString, string in_sql)
        {
            return ExecuteNonQueryTransaction(in_ConnectionString, in_sql, null, true);

        }

        public static bool ExecuteNonQueryTransaction(string in_ConnectionString, Dictionary<string, OracleParameter[]> listParams)
        {
            using (OracleConnection cn = new OracleConnection(in_ConnectionString))
            {
                cn.Open();
                OracleTransaction tran = cn.BeginTransaction();
                try
                {
                    foreach (KeyValuePair<string, OracleParameter[]> kv in listParams)
                    {
                        tran.ClExecuteNonQuery(kv.Key, kv.Value);
                    }
                    tran.Commit();
                    return true;

                }
                catch
                {
                    tran.Rollback();
                    return false;
                }
                finally
                {
                    cn.Close();
                }

            }
        }

        #endregion


        #region CheckIsEmpty

        /// <summary>
        /// 判断SQL命令查询的结果是否存在
        /// </summary>
        /// <param name="in_sql"></param>
        /// <param name="in_paras"></param>
        /// <returns></returns>
        public static bool CheckIsEmpty(string in_ConnectionString, string in_sql, OracleParameter[] in_paras)
        {
            int counts = Convert.ToInt32(ExecuteScalar(in_ConnectionString, in_sql, in_paras));
            if (counts == 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 判断SQL命令查询的结果是否为空
        /// </summary>
        /// <param name="in_sql"></param>
        /// <returns></returns>
        public static bool CheckIsEmpty(string in_ConnectionString, string in_sql)
        {
            return CheckIsEmpty(in_ConnectionString, in_sql, null);
        }

        #endregion


        #region IsCanDelete

        /// <summary>
        /// 是否能删除数据
        /// </summary>
        /// <returns>true:能删除数据  ,false:不能删除数据</returns>
        public static bool IsCanDelete(string in_ConnectionString, string deleteSql, params OracleParameter[] cmdParms)
        {
            try
            {
                ExecuteNonQueryTransaction(in_ConnectionString, deleteSql, cmdParms, false);
                return true;
            }
            catch
            {
                return false;
            }
        }

        #endregion


        #region GetPageData

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_paras">sql参数</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <param name="dropRowID">是否删除row_number排序号</param>
        /// <returns></returns>
        public static DataTable GetPageData(string in_ConnectionString, string in_Sql, OracleParameter[] in_paras, int in_PageSize, int in_PageNum, bool dropRowID, out int out_Rows, out int out_Pages)
        {
            using (OracleConnection cn = new OracleConnection(in_ConnectionString))
            {
                cn.Open();

                return cn.ClGetPageData(in_Sql, in_paras, in_PageSize, in_PageNum, dropRowID, out out_Rows, out out_Pages);
            }
        }

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_paras">sql参数</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <returns></returns>
        public static DataTable GetPageData(string in_ConnectionString, string in_Sql, OracleParameter[] in_paras, int in_PageSize, int in_PageNum, out int out_Rows, out int out_Pages)
        {
            return GetPageData(in_ConnectionString, in_Sql, in_paras, in_PageSize, in_PageNum, true, out out_Rows, out out_Pages);
        }

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <returns></returns>
        public static DataTable GetPageData(string in_ConnectionString, string in_Sql, int in_PageSize, int in_PageNum, out int out_Rows, out int out_Pages)
        {
            return GetPageData(in_ConnectionString, in_Sql, null, in_PageSize, in_PageNum, true, out out_Rows, out out_Pages);
        }

        #endregion


        #endregion

        #region Connection

        #region ClFromFile

        /// <summary>
        /// 从XML文件获取连接字符串并创建连接
        /// </summary>
        /// <param name="in_cn"></param>
        /// <param name="in_XmlFile"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        public static OracleConnection ClFromFile(this OracleConnection in_cn, string in_XmlFile, int index)
        {
            in_cn.ConnectionString = NetOraclePublicDAL.GetConnectionString(in_XmlFile, index);
            in_cn.Open();
            return in_cn;
        }

        /// <summary>
        /// 从XML文件获取连接字符串并创建连接
        /// </summary>
        /// <param name="in_cn"></param>
        /// <param name="in_XmlFile"></param>
        /// <returns></returns>
        public static OracleConnection ClFromFile(this OracleConnection in_cn, string in_XmlFile)
        {
            in_cn.ConnectionString = NetOraclePublicDAL.GetConnectionString(in_XmlFile);
            in_cn.Open();
            return in_cn;
        }

        /// <summary>
        /// 从XML文件获取连接字符串并创建连接
        /// </summary>
        /// <param name="in_cn"></param>
        /// <param name="in_XmlFile"></param>
        /// <param name="in_Description"></param>
        /// <returns></returns>
        public static OracleConnection ClFromFile(this OracleConnection in_cn, string in_XmlFile, string in_Description)
        {
            in_cn.ConnectionString = NetOraclePublicDAL.GetConnectionString(in_XmlFile, in_Description);
            in_cn.Open();
            return in_cn;
        }

        #endregion


        #region ClExecuteNonQuery

        public static int ClExecuteNonQuery(this OracleConnection cn, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            return NetOracleHelper.ExecuteNonQuery(cn, cmdType, in_sql, in_paras);
        }

        public static int ClExecuteNonQuery(this OracleConnection cn, CommandType cmdType, string in_sql)
        {
            return cn.ClExecuteNonQuery(cmdType, in_sql, null);
        }

        public static int ClExecuteNonQuery(this OracleConnection cn, string in_sql, OracleParameter[] in_paras)
        {
            return cn.ClExecuteNonQuery(CommandType.Text, in_sql, in_paras);
        }

        public static int ClExecuteNonQuery(this OracleConnection cn, string in_sql)
        {
            return cn.ClExecuteNonQuery(in_sql, null);
        }

        #endregion


        #region ClExecuteScalar

        public static object ClExecuteScalar(this OracleConnection cn, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            return NetOracleHelper.ExecuteScalar(cn, cmdType, in_sql, in_paras);
        }

        public static object ClExecuteScalar(this OracleConnection cn, CommandType cmdType, string in_sql)
        {
            return cn.ClExecuteScalar(cmdType, in_sql, null);
        }

        public static object ClExecuteScalar(this OracleConnection cn, string in_sql, OracleParameter[] in_paras)
        {
            return cn.ClExecuteScalar(CommandType.Text, in_sql, in_paras);
        }

        public static object ClExecuteScalar(this OracleConnection cn, string in_sql)
        {
            return cn.ClExecuteScalar(in_sql, null);
        }

        #endregion


        #region ClExecuteDataSet

        public static DataSet ClExecuteDataSet(this OracleConnection cn, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            return NetOracleHelper.ExecuteDataSet(cn, cmdType, in_sql, in_paras);
        }

        public static DataSet ClExecuteDataSet(this OracleConnection cn, CommandType cmdType, string in_sql)
        {
            return cn.ClExecuteDataSet(cmdType, in_sql, null);
        }

        public static DataSet ClExecuteDataSet(this OracleConnection cn, string in_sql, OracleParameter[] in_paras)
        {
            return cn.ClExecuteDataSet(CommandType.Text, in_sql, in_paras);
        }

        public static DataSet ClExecuteDataSet(this OracleConnection cn, string in_sql)
        {
            return cn.ClExecuteDataSet(in_sql, null);
        }

        #endregion


        #region ClExecuteDataTable

        public static DataTable ClExecuteDataTable(this OracleConnection cn, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            DataSet ds = cn.ClExecuteDataSet(cmdType, in_sql, in_paras);
            if (ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }

        public static DataTable ClExecuteDataTable(this OracleConnection cn, CommandType cmdType, string in_sql)
        {
            return cn.ClExecuteDataTable(cmdType, in_sql, null);
        }

        public static DataTable ClExecuteDataTable(this OracleConnection cn, string in_sql, OracleParameter[] in_paras)
        {
            return cn.ClExecuteDataTable(CommandType.Text, in_sql, in_paras);
        }

        public static DataTable ClExecuteDataTable(this OracleConnection cn, string in_sql)
        {
            return cn.ClExecuteDataTable(in_sql, null);
        }

        #endregion


        #region ExecuteReader 用完后一定要使用reader.Close

        public static OracleDataReader ExecuteReader(this OracleConnection cn, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            return NetOracleHelper.ExecuteReader(cn, cmdType, in_sql, in_paras);
        }

        public static OracleDataReader ExecuteReader(this OracleConnection cn, CommandType cmdType, string in_sql)
        {
            return cn.ExecuteReader(cmdType, in_sql, null);
        }

        public static OracleDataReader ExecuteReader(this OracleConnection cn, string in_sql, OracleParameter[] in_paras)
        {
            return cn.ExecuteReader(CommandType.Text, in_sql, in_paras);
        }

        public static OracleDataReader ExecuteReader(this OracleConnection cn, string in_sql)
        {
            return cn.ExecuteReader(in_sql, null);
        }

        #endregion


        #region ClExecuteNonQueryTransaction

        public static int ClExecuteNonQueryTransaction(this OracleConnection cn, CommandType cmdType, string in_sql, OracleParameter[] in_paras, bool in_commit)
        {
            OracleTransaction tran = cn.BeginTransaction();

            int i = tran.ClExecuteNonQuery(cmdType, in_sql, in_paras);


            if ((i >= 0) && in_commit)
            {
                tran.Commit();
                return i;
            }

            tran.Rollback();
            return i;

        }

        public static int ClExecuteNonQueryTransaction(this OracleConnection cn, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            return cn.ClExecuteNonQueryTransaction(cmdType, in_sql, in_paras, true);
        }

        public static int ClExecuteNonQueryTransaction(this OracleConnection cn, CommandType cmdType, string in_sql)
        {
            return cn.ClExecuteNonQueryTransaction(cmdType, in_sql, null);
        }

        public static int ClExecuteNonQueryTransaction(this OracleConnection cn, string in_sql, OracleParameter[] in_paras, bool in_commit)
        {
            return cn.ClExecuteNonQueryTransaction(CommandType.Text, in_sql, in_paras, in_commit);
        }

        public static int ClExecuteNonQueryTransaction(this OracleConnection cn, string in_sql, OracleParameter[] in_paras)
        {
            return cn.ClExecuteNonQueryTransaction(in_sql, in_paras, true);
        }

        public static int ClExecuteNonQueryTransaction(this OracleConnection cn, string in_sql)
        {
            return cn.ClExecuteNonQueryTransaction(in_sql, null, true);

        }

        public static bool ClExecuteNonQueryTransaction(this OracleConnection cn, Dictionary<string, OracleParameter[]> listParams)
        {
            OracleTransaction tran = cn.BeginTransaction();
            try
            {
                foreach (KeyValuePair<string, OracleParameter[]> kv in listParams)
                {
                    tran.ClExecuteNonQuery(kv.Key, kv.Value);
                }
                tran.Commit();
                return true;

            }
            catch
            {
                tran.Rollback();
                return false;
            }
            finally
            {
                cn.Close();
            }
        }

        #endregion


        #region CheckIsEmpty

        /// <summary>
        /// 判断SQL命令查询的结果是否存在
        /// </summary>
        /// <param name="in_sql"></param>
        /// <param name="in_paras"></param>
        /// <returns></returns>
        public static bool CheckIsEmpty(this OracleConnection cn, string in_sql, OracleParameter[] in_paras)
        {
            int counts = Convert.ToInt32(cn.ClExecuteScalar(in_sql, in_paras));
            if (counts == 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 判断SQL命令查询的结果是否为空
        /// </summary>
        /// <param name="in_sql"></param>
        /// <returns></returns>
        public static bool CheckIsEmpty(this OracleConnection cn, string in_sql)
        {
            return cn.CheckIsEmpty(in_sql, null);
        }

        #endregion


        #region IsCanDelete

        /// <summary>
        /// 是否能删除数据
        /// </summary>
        /// <returns>true:能删除数据  ,false:不能删除数据</returns>
        public static bool IsCanDelete(this OracleConnection cn, string deleteSql, params OracleParameter[] cmdParms)
        {
            try
            {
                cn.ClExecuteNonQueryTransaction(deleteSql, cmdParms, false);
                return true;
            }
            catch
            {
                return false;
            }
        }

        #endregion


        #region GetPageData

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_paras">sql参数</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <param name="dropRowID">是否删除row_number排序号</param>
        /// <returns></returns>
        public static DataTable ClGetPageData(this OracleConnection cn, string in_Sql, OracleParameter[] in_paras, int in_PageSize, int in_PageNum, bool dropRowID, out int out_Rows, out int out_Pages)
        {
            int m_PageSize = in_PageSize;
            if (m_PageSize <= 0)
            {
                m_PageSize = 10;
            }
            int m_PageNum = in_PageNum;
            if (m_PageNum <= 0)
            {
                m_PageNum = 1;
            }

            string m_Cte =
@"with CTE
as (
     select ROWNUM as row_number, st.*
     from   
     (
       {0}
     ) st
)
";
            m_Cte = m_Cte.ClFormat(in_Sql);

            string RsString = m_Cte + "select count(1) from CTE";
            out_Rows = Convert.ToInt32(cn.ClExecuteScalar(RsString, in_paras));

            out_Pages = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(out_Rows) / Convert.ToDouble(m_PageSize)));

            RsString = m_Cte + "select * from CTE where row_number > {0} and row_number <= {1} ";
            RsString = RsString.ClFormat(m_PageSize * (m_PageNum - 1), m_PageSize * m_PageNum);

            DataTable dataTable = cn.ClExecuteDataTable(RsString, in_paras);

            if (dropRowID)
            {
                dataTable.Columns.Remove("row_number");
            }
            return dataTable;
        }

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_paras">sql参数</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <returns></returns>
        public static DataTable ClGetPageData(this OracleConnection cn, string in_Sql, OracleParameter[] in_paras, int in_PageSize, int in_PageNum, out int out_Rows, out int out_Pages)
        {
            return cn.ClGetPageData(in_Sql, in_paras, in_PageSize, in_PageNum, true, out out_Rows, out out_Pages);
        }

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <returns></returns>
        public static DataTable ClGetPageData(this OracleConnection cn, string in_Sql, int in_PageSize, int in_PageNum, out int out_Rows, out int out_Pages)
        {
            return cn.ClGetPageData(in_Sql, null, in_PageSize, in_PageNum, true, out out_Rows, out out_Pages);
        }
        #endregion

        #endregion

        #region Tran

        #region ClExecuteNonQuery

        public static int ClExecuteNonQuery(this OracleTransaction tran, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            return NetOracleHelper.ExecuteNonQuery(tran, cmdType, in_sql, in_paras);
        }

        public static int ClExecuteNonQuery(this OracleTransaction tran, CommandType cmdType, string in_sql)
        {
            return tran.ClExecuteNonQuery(cmdType, in_sql, null);
        }

        public static int ClExecuteNonQuery(this OracleTransaction tran, string in_sql, OracleParameter[] in_paras)
        {
            return tran.ClExecuteNonQuery(CommandType.Text, in_sql, in_paras);
        }

        public static int ClExecuteNonQuery(this OracleTransaction tran, string in_sql)
        {
            return tran.ClExecuteNonQuery(in_sql, null);
        }

        #endregion


        #region ClExecuteScalar

        public static object ClExecuteScalar(this OracleTransaction tran, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            return NetOracleHelper.ExecuteScalar(tran, cmdType, in_sql, in_paras);
        }

        public static object ClExecuteScalar(this OracleTransaction tran, CommandType cmdType, string in_sql)
        {
            return tran.ClExecuteScalar(cmdType, in_sql, null);
        }

        public static object ClExecuteScalar(this OracleTransaction tran, string in_sql, OracleParameter[] in_paras)
        {
            return tran.ClExecuteScalar(CommandType.Text, in_sql, in_paras);
        }

        public static object ClExecuteScalar(this OracleTransaction tran, string in_sql)
        {
            return tran.ClExecuteScalar(in_sql, null);
        }

        #endregion


        #region ClExecuteDataSet

        public static DataSet ClExecuteDataSet(this OracleTransaction tran, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            return NetOracleHelper.ExecuteDataSet(tran, cmdType, in_sql, in_paras);
        }

        public static DataSet ClExecuteDataSet(this OracleTransaction tran, CommandType cmdType, string in_sql)
        {
            return tran.ClExecuteDataSet(cmdType, in_sql, null);
        }

        public static DataSet ClExecuteDataSet(this OracleTransaction tran, string in_sql, OracleParameter[] in_paras)
        {
            return tran.ClExecuteDataSet(CommandType.Text, in_sql, in_paras);
        }

        public static DataSet ClExecuteDataSet(this OracleTransaction tran, string in_sql)
        {
            return tran.ClExecuteDataSet(in_sql, null);
        }


        #endregion


        #region ClExecuteDataTable

        public static DataTable ClExecuteDataTable(this OracleTransaction tran, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            DataSet ds = tran.ClExecuteDataSet(cmdType, in_sql, in_paras);
            if (ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }

        public static DataTable ClExecuteDataTable(this OracleTransaction tran, CommandType cmdType, string in_sql)
        {
            return tran.ClExecuteDataTable(cmdType, in_sql, null);
        }

        public static DataTable ClExecuteDataTable(this OracleTransaction tran, string in_sql, OracleParameter[] in_paras)
        {
            return tran.ClExecuteDataTable(CommandType.Text, in_sql, in_paras);
        }

        public static DataTable ClExecuteDataTable(this OracleTransaction tran, string in_sql)
        {
            return tran.ClExecuteDataTable(in_sql, null);
        }

        #endregion


        #region ExecuteReader 用完后一定要使用reader.Close

        public static OracleDataReader ExecuteReader(this OracleTransaction tran, CommandType cmdType, string in_sql, OracleParameter[] in_paras)
        {
            return NetOracleHelper.ExecuteReader(tran, cmdType, in_sql, in_paras);
        }

        public static OracleDataReader ExecuteReader(this OracleTransaction tran, CommandType cmdType, string in_sql)
        {
            return tran.ExecuteReader(cmdType, in_sql, null);
        }

        public static OracleDataReader ExecuteReader(this OracleTransaction tran, string in_sql, OracleParameter[] in_paras)
        {
            return tran.ExecuteReader(CommandType.Text, in_sql, in_paras);
        }

        public static OracleDataReader ExecuteReader(this OracleTransaction tran, string in_sql)
        {
            return tran.ExecuteReader(in_sql, null);
        }

        #endregion


        #region CheckIsEmpty

        /// <summary>
        /// 判断SQL命令查询的结果是否存在
        /// </summary>
        /// <param name="in_sql"></param>
        /// <param name="in_paras"></param>
        /// <returns></returns>
        public static bool CheckIsEmpty(this OracleTransaction tran, string in_sql, OracleParameter[] in_paras)
        {
            int counts = Convert.ToInt32(tran.ClExecuteScalar(in_sql, in_paras));
            if (counts == 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 判断SQL命令查询的结果是否为空
        /// </summary>
        /// <param name="in_sql"></param>
        /// <returns></returns>
        public static bool CheckIsEmpty(this OracleTransaction tran, string in_sql)
        {
            return tran.CheckIsEmpty(in_sql, null);
        }

        #endregion


        #region GetPageData

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_paras">sql参数</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <param name="dropRowID">是否删除row_number排序号</param>
        /// <returns></returns>
        public static DataTable ClGetPageData(this OracleTransaction tran, string in_Sql, OracleParameter[] in_paras, int in_PageSize, int in_PageNum, bool dropRowID, out int out_Rows, out int out_Pages)
        {
            int m_PageSize = in_PageSize;
            if (m_PageSize <= 0)
            {
                m_PageSize = 10;
            }
            int m_PageNum = in_PageNum;
            if (m_PageNum <= 0)
            {
                m_PageNum = 1;
            }

            string m_Cte =
@"with CTE
as (
     select ROWNUM as row_number, st.*
     from   
     (
       {0}
     ) st
)
";
            m_Cte = m_Cte.ClFormat(in_Sql);

            string RsString = m_Cte + "select count(1) from CTE";
            out_Rows = Convert.ToInt32(tran.ClExecuteScalar(RsString, in_paras));

            out_Pages = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(out_Rows) / Convert.ToDouble(m_PageSize)));

            RsString = m_Cte + "select * from CTE where row_number > {0} and row_number <= {1} ";
            RsString = RsString.ClFormat(m_PageSize * (m_PageNum - 1), m_PageSize * m_PageNum);

            DataTable dataTable = tran.ClExecuteDataTable(RsString, in_paras);

            if (dropRowID)
            {
                dataTable.Columns.Remove("row_number");
            }
            return dataTable;
        }

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_paras">sql参数</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <returns></returns>
        public static DataTable ClGetPageData(this OracleTransaction tran, string in_Sql, OracleParameter[] in_paras, int in_PageSize, int in_PageNum, out int out_Rows, out int out_Pages)
        {
            return tran.ClGetPageData(in_Sql, in_paras, in_PageSize, in_PageNum, true, out out_Rows, out out_Pages);
        }

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <returns></returns>
        public static DataTable ClGetPageData(this OracleTransaction tran, string in_Sql, int in_PageSize, int in_PageNum, out int out_Rows, out int out_Pages)
        {
            return tran.ClGetPageData(in_Sql, null, in_PageSize, in_PageNum, true, out out_Rows, out out_Pages);
        }
        #endregion


        #endregion


    }

}
